package com.hdw.med.service;

import com.alibaba.fastjson.JSONObject;
import com.hdw.common.base.BaseException;
import com.hdw.common.base.BaseResult;
import com.hdw.common.base.api.BaseMsgService;
import com.hdw.common.constant.CommonConstant;
import com.hdw.common.db.DynamicDataSource;
import com.hdw.common.db.HikariDataSourceExt;
import com.hdw.common.util.DBUtil;
import com.hdw.common.util.SnowflakeIdWorker;
import com.hdw.med.MedConstant;
import com.hdw.med.api.*;
import com.hdw.med.bean.model.*;
import com.hdw.med.bean.vo.AlterTableParamVO;
import com.hdw.med.mapper.MdMedTblogDtMapper;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class CommonTableDDLServiceImpl implements CommonTableDDLService {

    private static final Logger log = LoggerFactory.getLogger(CommonTableDDLServiceImpl.class);

    @Autowired
    private BaseDbTpcompService baseDbTpcompService;
    @Autowired
    private MdMedTbService mdMedTbService;
    @Autowired
    private MdMedTbFldService mdMedTbFldService;
    @Autowired
    private MdMedTblogService mdMedTblogService;
    @Autowired
    private MdMedPubfldService mdMedPubfldService;
    @Autowired
    MdMedTblogDtMapper mdMedTblogDtMapper;
    @Autowired
    BaseMsgService baseMsgService;

    @Override
    public BaseResult<String> dropTableByMed(String cdTb, Long idTblog) {
        return this.dropTableByMedOtherDB(cdTb, idTblog, DBUtil.DB_DW);
    }

    @Override
    public BaseResult<String> dropTableByMedOtherDB(String cdTb, Long idTblog, String dataSourceId) {
        String sql = " TRUNCATE table "+cdTb+";DROP TABLE " + cdTb;
        MdMedTb mdMedTb = mdMedTbService.getByCd(cdTb);
        if (mdMedTb == null) {
            return BaseResult.success(null, "表不存在");
        }
        MdMedTblog tblog = null;
        if (idTblog == null) {
            tblog = new MdMedTblog();
            tblog.setCdTb(cdTb);
            tblog.setNaTp("删除");
            tblog.setEuStatus((byte) 0);
            mdMedTblogService.insertReturnId(tblog);
        } else {
            tblog = mdMedTblogService.getById(idTblog);
        }
        BaseResult result = execDDLSQL(sql, dataSourceId);
        if (BaseResult.isSuccess(result)) {
            mdMedTbService.deleteById(mdMedTb.getIdTb());
            mdMedTbFldService.deleteById(mdMedTb.getIdTb());
            tblog.setEuStatus((byte) 1);
        } else {
            baseMsgService.insert(CommonConstant.MSG_CODE_B01,"表"+cdTb+"删除失败","操作记录主键为"+tblog.getIdTblog());
        }
        mdMedTblogService.updateByIdSelective(tblog);
        return result;
    }

    @Override
    public boolean alterTableByMed(AlterTableParamVO paramVO) throws BaseException {
        return this.alterTableByMedOtherDB(paramVO, DBUtil.DB_DW);
    }

    @Override
    public boolean alterTableByMedOtherDB(AlterTableParamVO paramVO, String dataSourceId) throws BaseException {
        MdMedTb newTb = paramVO.getNewTb();
        List<MdMedTbFld> newtbFldList = paramVO.getNewtbFldList();
        String oldTableName = paramVO.getOldTableName();
        MdMedTblog tblog = null;
        if (paramVO.getIdTblog() == null) {
            tblog = new MdMedTblog();
            tblog.setCdTb(newTb.getCd());
            tblog.setNaTp("修改");
            tblog.setEuStatus((byte) 0);
            tblog.setMethod("alterTableByMed");
            tblog.setParam(JSONObject.toJSONString(paramVO));
            tblog.setEuParamtp(MedConstant.PARAM_TYPE_JSON);
            mdMedTblogService.insertReturnId(tblog);
        } else {
            tblog = mdMedTblogService.getById(paramVO.getIdTblog());
        }
        setFldLthAndPrec(newtbFldList);
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceId);
        String euTp = dataSource == null ? null : dataSource.getEuTp();
        MdMedTb oldTb = mdMedTbService.getByCd(newTb.getCd());
        if (!StringUtils.isEmpty(oldTableName)) oldTb = mdMedTbService.getByCd(oldTableName);

        boolean isSuccess = true, lastStatus = true;
        if (oldTb == null) { //执行表创建
            newTb.setIdTb(SnowflakeIdWorker.getId());
            tblog.setNaTp("创建");
            isSuccess = getCreateTbSql(newTb, newtbFldList, tblog.getIdTblog(), dataSourceId);
            if (isSuccess) {
                mdMedTbService.insert(newTb);
                for (MdMedTbFld fld : newtbFldList) {
                    fld.setIdTb(newTb.getIdTb());
                    mdMedTbFldService.insert(fld);
                }
            }
            lastStatus = isSuccess;
        } else if (oldTb != null) {
            newTb.setIdTb(oldTb.getIdTb());
            if (!StringUtils.isEmpty(oldTableName)) { //重命名表
                isSuccess = tbRenameSql(newTb.getCd(), oldTableName, tblog.getIdTblog(), dataSourceId);
                if (isSuccess) {
                    newTb.setCd(newTb.getCd());
                    mdMedTbService.updateByIdSelective(newTb);
                } else {
                    lastStatus = false;
                }
            }
            for (MdMedTbFld fld : newtbFldList) {
                fld.setIdTb(newTb.getIdTb());
            }
            isSuccess = getFldSql(newtbFldList, newTb, euTp, tblog.getIdTblog(), dataSourceId); //执行字段的增删改
            if (!isSuccess) {
                lastStatus = false;
            }
        }
        if (lastStatus) {
            tblog.setEuStatus((byte) 1);
            mdMedTblogService.updateByIdSelective(tblog);
        } else {
            baseMsgService.insert(CommonConstant.MSG_CODE_B01,"表"+newTb.getCd()+"操作失败","操作记录主键为"+tblog.getIdTblog());
        }
        return lastStatus;
    }

    private Boolean getFldSql(List<MdMedTbFld> newtbFldList, MdMedTb mdMedTb, String euTp, Long idTblog, String dataSourceId) {
        BaseResult result;
        Boolean lastStatus = true;
        try {
            MdMedTbFld fldModel = new MdMedTbFld();
            fldModel.setIdTb(mdMedTb.getIdTb());
            Map<String, MdMedTbFld> oldtbFldList = mdMedTbFldService.findMapDataByModel(fldModel);
            MdMedTbFld oldTbFld;
            String idPubfld, tbFldTp;
            StringBuffer alterSql = new StringBuffer();
            Map<Integer, BaseDbTpcomp> dbTpCompMap = baseDbTpcompService.findByDbTypeMap(euTp);
            Integer oldNumLth, newNumLth, oldNumPrec, newNumPrec;
            boolean isSuccess = true;
            for (MdMedTbFld newFld : newtbFldList) {
                idPubfld = newFld.getIdPubfld();
                BaseDbTpcomp baseDbTpcomp = dbTpCompMap.get(newFld.getEuJavatp());
                tbFldTp = baseDbTpcomp.getEuFldtp();
                String column = tbFldTp + getColumnType(newFld.getNumLth(), newFld.getNumPrec(), baseDbTpcomp.getEuUsePrecision());

                if ((oldTbFld = oldtbFldList.get(idPubfld)) == null) { //新增


                    isSuccess = tableFldAlterSql(0, euTp, mdMedTb.getCd(), idPubfld, column, idTblog, dataSourceId);
                    if (isSuccess) {
                        mdMedTbFldService.insert(newFld);
                    } else {
                        lastStatus = false;
                    }
                } else {
                    oldNumLth = oldTbFld.getNumLth() == null ? 0 : oldTbFld.getNumLth();
                    oldNumPrec = oldTbFld.getNumPrec() == null ? 0 : oldTbFld.getNumPrec();
                    newNumPrec = newFld.getNumPrec() == null ? 0 : newFld.getNumPrec();
                    newNumLth = newFld.getNumLth() == null ? 0 : newFld.getNumLth();
                    if ((newNumLth > oldNumLth && newNumPrec >= oldNumPrec) || (newNumLth >= oldNumLth && newNumPrec > oldNumPrec)) { //长度与精度只可调大，不可调小
                        isSuccess = tableFldAlterSql(1, euTp, mdMedTb.getCd(), idPubfld, column, idTblog, dataSourceId);
                        if (isSuccess) {
                            mdMedTbFldService.updateByIdSelective(newFld);
                        } else {
                            lastStatus = false;
                        }
                    }
                }
                oldtbFldList.remove(idPubfld);
            }
            for (Map.Entry<String, MdMedTbFld> entry : oldtbFldList.entrySet()) {
                isSuccess = tableFldAlterSql(2, euTp, mdMedTb.getCd(), entry.getKey(), null, idTblog, dataSourceId);
                if (isSuccess) {
                    mdMedTbFldService.deleteById(entry.getValue().getIdTbFld());
                } else {
                    lastStatus = false;
                }
            }
        } catch (Exception e) {
            log.error("表"+mdMedTb.getCd()+"操作失败",e);
            lastStatus = false;
        }
        return lastStatus;
    }

    private void setFldLthAndPrec(List<MdMedTbFld> newtbFldList) {
        List<String> idPubfldList = new ArrayList<>();
        for (MdMedTbFld fld : newtbFldList) {
            idPubfldList.add(fld.getIdPubfld());
        }
        Map<String, MdMedPubfld> pubfldList = mdMedPubfldService.findMapDataByIdList(idPubfldList);
        MdMedPubfld pubfld;
        for (MdMedTbFld fld : newtbFldList) {
            pubfld = pubfldList.get(fld.getIdPubfld());
            if (pubfld != null) {
                fld.setNumLth(pubfld.getNumLth());
                fld.setNumPrec(pubfld.getNumPrec());
                fld.setEuJavatp(pubfld.getEuJavatp());
                fld.setNaJavatp(pubfld.getNaJavatp());
                fld.setNaPubfld(pubfld.getNa());
            }
        }

    }

    private Boolean tableFldAlterSql(int operTp, String dbTp, String tableName, String column, String colType, Long idTblog, String dataSourceId) {
        String sql = null;
        String nameType = "";
        switch (dbTp) {
            case DBUtil.DB_TYPE_MYSQL:
                if (operTp == 0) { //新增
                    nameType = "新增";
                    sql = "ALTER TABLE " + tableName + " ADD COLUMN " + column + " " + colType;
                } else if (operTp == 1) { //修改
                    nameType = "修改";
                    sql = "ALTER TABLE " + tableName + " modify COLUMN " + column + " " + colType;
                } else if (operTp == 2) { //删除
                    nameType = "删除";
                    sql = "ALTER TABLE " + tableName + " DROP COLUMN  " + column;
                }
                break;
            case DBUtil.DB_TYPE_SQLSERVER:
                if (operTp == 0) { //新增
                    nameType = "新增";
                    sql = "ALTER TABLE " + tableName + " ADD  " + column + " " + colType;
                } else if (operTp == 1) { //修改
                    nameType = "修改";
                    sql = "ALTER TABLE " + tableName + " ALTER COLUMN " + column + " " + colType;
                } else if (operTp == 2) { //删除
                    nameType = "删除";
                    sql = "ALTER TABLE " + tableName + " DROP COLUMN  " + column;
                }
                break;
            case DBUtil.DB_TYPE_ORACLE:
                if (operTp == 0) { //新增
                    nameType = "新增";
                    sql = "ALTER TABLE " + tableName + " ADD " + column + " " + colType;
                } else if (operTp == 1) { //修改
                    nameType = "修改";
                    sql = "ALTER TABLE " + tableName + " modify " + column + " " + colType;
                } else if (operTp == 2) { //删除
                    nameType = "删除";
                    sql = "ALTER TABLE " + tableName + " DROP COLUMN  " + column;
                }
                break;
        }
        BaseResult result = execDDLSQL(sql, dataSourceId);
        Boolean isSuccess = true;
        if (BaseResult.isSuccess(result)) {
            saveDt(idTblog, "表" + tableName + nameType + "字段" + column, sql, 1, null);
        } else {
            saveDt(idTblog, "表" + tableName + nameType + "字段" + column, sql, 0, result.getMsg());
            isSuccess = false;
        }
        return isSuccess;
    }

    private String getColumnType(Integer numLth, Integer numPrec, Integer euUsePrecision) {
        String type = "";
        if (euUsePrecision != 1) return type;

        if (numLth != null && numLth > 0) {
            if (numPrec != null && numPrec > 0) {
                type = "(" + numLth + "," + numPrec + ")";
            } else {
                type = "(" + numLth + ")";
            }
        }
        return type;
    }

    private Boolean getCreateTbSql(MdMedTb newTb, List<MdMedTbFld> newtbFldList, Long idTblog, String dataSourceId) throws BaseException {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceId);
        Map<Integer, BaseDbTpcomp> dbTpCompMap = baseDbTpcompService.findByDbTypeMap(dataSource.getEuTp());

        StringBuffer sb = new StringBuffer("create table " + newTb.getCd() + "(");


        for (MdMedTbFld fld : newtbFldList) {
            BaseDbTpcomp baseDbTpcomp = dbTpCompMap.get(fld.getEuJavatp());
            String tbFldTp = baseDbTpcomp.getEuFldtp();
            if (StringUtils.isEmpty(tbFldTp)) {
                throw new BaseException(dataSourceId + "数据库类型中的" + fld.getEuJavatp() + "未参与对照请完善");
            }
            sb.append(fld.getIdPubfld() + " " + tbFldTp);
            if (fld.getNumLth() != null && fld.getNumLth() > 0 && baseDbTpcomp.getEuUsePrecision() == 1) {
                if (fld.getNumPrec() != null && fld.getNumPrec() > 0) {
                    sb.append("(" + fld.getNumLth() + "," + fld.getNumPrec() + ")");
                } else {
                    sb.append("(" + fld.getNumLth() + ")");
                }
            }
            if (MedConstant.SD_CONSTP_PK.equals(fld.getSdConstp())) {
                sb.append(" primary key ");
            }
            sb.append(",");
        }
        sb.append("dt_sys_cre " + dbTpCompMap.get(93).getEuFldtp() + ",");
        sb.append("dt_sys_modi " + dbTpCompMap.get(93).getEuFldtp() + ");");
        BaseResult result = execDDLSQL(sb.toString(), dataSourceId);
        Boolean isSuccess = false;
        if (BaseResult.isSuccess(result)) {
            saveDt(idTblog, "表" + newTb.getNa() + "创建", sb.toString(), 1, null);
            isSuccess = true;
        } else {
            saveDt(idTblog, "表" + newTb.getNa() + "创建", sb.toString(), 0, result.getMsg());
        }
        result.setData(sb.toString());
        return isSuccess;
    }

    private Boolean tbRenameSql(String newTableName, String oldTableName, Long idTblog, String dataSourceId) {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceId);
        String euDbTp = dataSource == null ? null : dataSource.getEuTp();
        String renameSql = "";
        switch (euDbTp) {
            case DBUtil.DB_TYPE_MYSQL:
                renameSql = "RENAME TABLE " + oldTableName + " TO " + newTableName + ";";
                break;
            case DBUtil.DB_TYPE_SQLSERVER:
                renameSql = "EXEC sp_rename " + oldTableName + "," + newTableName + ";";
                break;
            case DBUtil.DB_TYPE_ORACLE:
                renameSql = "RENAME " + oldTableName + " TO " + newTableName + ";";
                break;
        }
        if (StringUtils.isEmpty(renameSql)) {
            BaseResult.error("数据库类型" + euDbTp + "不存在，无法执行表的重命名操作");
        }
        BaseResult result = execDDLSQL(renameSql, dataSourceId);
        Boolean isSuccess = false;
        if (BaseResult.isSuccess(result)) {
            saveDt(idTblog, "表" + oldTableName + "更名为" + newTableName, renameSql, 1, null);
            isSuccess = true;
        } else {
            saveDt(idTblog, "表" + oldTableName + "创建" + newTableName, renameSql, 0, result.getMsg());
        }
        return isSuccess;
    }

    /**
     * 表的create、alter、drop
     *
     * @param sqls
     * @param dataSourceName
     * @throws BaseException
     */
    public BaseResult execDDLSQL(String sqls, String dataSourceName) {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceName);
        if (dataSource == null) {
            BaseResult.error(String.format("获取目标数据库[%s]失败", dataSourceName));
        }
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);
            List<String> sqlList = new ArrayList<>();
            for (String sql : sqls.split(";")) {
                if (!org.springframework.util.StringUtils.isEmpty(sql) && sql.trim().length() > 6) {
                    sqlList.add(sql);
                }
            }
            Statement statement = connection.createStatement();
            for (String beforeSql : sqlList) {
                statement.execute(beforeSql);
            }
            return BaseResult.success(null, "执行成功");
        } catch (Exception e) {
            return BaseResult.error("执行SQL错误! 错误信息为:" + e.getMessage());
        } finally {
            DBUtil.closeDBResources(null, preparedStatement, connection);
        }
    }

    private void saveDt(Long idTblog, String title, String sql, int euStatus, String des) {
        MdMedTblogDt tblogDt = new MdMedTblogDt();
        tblogDt.setIdTblog(idTblog);
        tblogDt.setTitle(title);
        tblogDt.setSqlExec(sql);
        tblogDt.setEuStatus((byte) euStatus);
        if (des != null && des.length() > 4000) {
            des = des.substring(0, 4000);
        }
        tblogDt.setDes(des);
        mdMedTblogDtMapper.insert(tblogDt);
    }
}